package com.pubpage.sys_upload_file;

import com.pub.database.*;
import java.util.*;

import javax.servlet.http.*;
import com.pub.web.*;
import org.apache.commons.fileupload.servlet.*;
import org.apache.commons.fileupload.disk.*;
import org.apache.commons.fileupload.*;
import com.pub.tools.*;


public class Business {
  QueryBean gqbnDAO = null;
  public Business(QueryBean pqbnDAO) {
    gqbnDAO = pqbnDAO;
  }
@SuppressWarnings("unchecked")
void getFileUploadData(View view,HttpServletRequest request) throws Exception{
    ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());
    FileItem item = null;
    List<Iterator<FileItem>> items = upload.parseRequest(request);
    Iterator iter = items.iterator();
    String strEncoding = "utf-8";
    try{
        //** add character Encoding **
        if(request.getCharacterEncoding()==null){
          upload.setHeaderEncoding("UTF-8");
        }
        else{
          upload.setHeaderEncoding(request.getCharacterEncoding());
        }

        while (iter.hasNext()) {
            item = (FileItem) iter.next();
            if (item.isFormField()) {
                if (item.getFieldName().equals("att_table")) {
                  view.setatt_table(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("flow_code")) {
                  view.setflow_code(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("flow_num")) {
                  view.setflow_num(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("file_knd")) {
                  view.setfile_knd(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("knd")) {
                  view.setknd(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("max_size")) {
                  view.setMax_size(Long.parseLong(item.getString(strEncoding)));
                }
                if (item.getFieldName().equals("att_ifrm_name")) {
                  view.setatt_ifrm_name(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("file_desc")) {
                  view.setfile_desc(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("prgAction")) {
                  view.setprgAction(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("file_storage")) {
                  view.setfile_storage(item.getString(strEncoding));
                }
                if (item.getFieldName().equals("file_path")) {
                  view.setfile_path(item.getString(strEncoding));
                }

            }else{
                view.setFile_data(item.get());
                view.setFile_name(AttachBean.getFilename(item.getName()));
                view.setFile_size(item.getSize());
                view.setMime_type(item.getContentType());
            }
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{

    }
}
void getAttInfoFromSession(View view,Hashtable<String,String> phtAttInfo) throws Exception{
    /**
     * 由於附檔要傳遞的資訊過多
     * (若用url參數(Form Method:GET)的方式，有可能會超過255字數的限制，用此法超過的字數會被截掉)
     * 於是透過一個中繼JSP，將參數設到session中
     * 再從session把資訊取出
     */
    view.setflow_num(phtAttInfo.get("flow_num").toString());
    view.setflow_code(phtAttInfo.get("flow_code").toString());
    view.setatt_table(phtAttInfo.get("att_table").toString());
    view.setMax_size(Long.parseLong(phtAttInfo.get("max_size").toString()));
    view.setfile_storage(phtAttInfo.get("file_storage").toString());
    view.setfile_path(phtAttInfo.get("file_path").toString());
    view.setatt_ifrm_name(phtAttInfo.get("att_ifrm_name").toString());
    view.setknd(phtAttInfo.get("knd").toString());
}
  /**
   * getData
   *
   * @param view View
   * @param pstrUserNO String
   * @return String
   */
  String getData(View view, String pstrUserNO) throws Exception{

      return getDataViewHtml(view.getDataView(),view.getDataViewForm(),view);
    }

  String getDataViewHtml(DataView dataView,DataViewForm DataViewForm,View view) throws Exception{
      //設定上一頁與下一頁按鈕的form action(通常為母頁即本頁)
      DataViewForm.setPageActionURI(getDataViewPageAction(view));

      //設定要query的table list(以逗號隔開)
      DataViewForm.setTablesList(view.getatt_table()+" att,sys_att_kind knd");

      //依欄位查詢值組合sql where
      DataViewForm.setSQLCondition(getDataViewWhere(view));

      //設定排序的欄位
      DataViewForm.setOrderByFields("att.knd,file_id");

      //排序的方法(正序或倒序)
      DataViewForm.setOrderBySeq("asc");

      //group by 的欄位(以逗號隔開)
      DataViewForm.setGroupBy(null);

      //Having 的欄位(有設定group by時此欄位才有作用)
      DataViewForm.setHaving(null);

      //將欄位相關設定值設入DataViewForm
      DataViewForm.setColRelationUseArrayList(getDataViewColRelation(dataView,view));

      //設定DataView Header的<tr> html屬性
      DataViewForm.setTableHeaderHtml("class='td_head_grid' align='center' width='640px'");

      DataViewForm.setJumpPageButtonShow(false);   //是否要秀出上下頁按鈕
      DataViewForm.setPageBtnOnTop(true);         //將button and rows info 秀在頂端
      DataViewForm.setPageBtnOnButtom(false);       //將button and rows info 秀在底端
      DataViewForm.setFilterShow(false);           //是否要秀出過濾選擇欄位
      DataViewForm.setJumpPageShow(false);         //是否要秀出跳頁下拉式選單
      DataViewForm.setRowsInfoShow(false);         //是否要秀出筆數資訊
      DataViewForm.setRowPageShow(false);          //是否要秀出每頁筆數控制欄
      DataViewForm.setExcelShow(false);            //是否要秀出轉Excel button
      DataViewForm.setOrderEnable(false);          //是否要提供排序點選功能
      DataViewForm.setShowHeaderWhenNoData(true);  //當ResultSet無資料是否要秀Header
      DataViewForm.setAddNewShow(false);           //是否秀出新增鈕

      return dataView.getOutputDataView(DataViewForm);
   }
   /**
    * getDataViewColRelation
    *
    * @param dataView DataView
    * @param view View
    * @return ArrayList
    */
   private ArrayList<Hashtable<String,Object>> getDataViewColRelation(DataView dataView, View view) {
       ArrayList<Hashtable<String,Object>> laltColRelation = new ArrayList<Hashtable<String,Object>> ();
       Hashtable<String,Object> lhtColRelationDetail = new Hashtable<String,Object>();
       ArrayList<String> laltLinkParamList=new ArrayList<String>();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "KND_DESC");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+view.getFile_kndTitle()+"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"KND_DESC");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "FILE_STORAGE");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "HIDDEN");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "INPUT");
       lhtColRelationDetail.put(dataView.STR_FIELD_HTML, "<INPUT type='hidden' name='file_storage' value=");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"FILE_STORAGE");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "ATT.FLOW_CODE");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "HIDDEN");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "INPUT");
       lhtColRelationDetail.put(dataView.STR_FIELD_HTML, "<INPUT type='hidden' name='flow_code%sn%' value=");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"FLOW_CODE");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "FILE_ID");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "HIDDEN");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "INPUT");
       lhtColRelationDetail.put(dataView.STR_FIELD_HTML, "<INPUT type='hidden' name='file_id%sn%' value=");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"FILE_ID");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "FILE_NAME");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'" + view.getFile_nameTitle() + "'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "LINK");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='25%'");
       lhtColRelationDetail.put(dataView.STR_FIELD_HTML,"<a target='_blank' href=");
       lhtColRelationDetail.put(dataView.STR_LINK_URI,"'/PUBPAGE/openAttach'");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE, "FILE_NAME");
       laltLinkParamList.add("db_alias=");
       laltLinkParamList.add("'"+view.getDB_Alias()+"'");
       laltLinkParamList.add("table=");
       laltLinkParamList.add("'"+view.getatt_table()+"'");
       laltLinkParamList.add("file_storage=");
       laltLinkParamList.add("FILE_STORAGE");
       laltLinkParamList.add("flow_code=");
       laltLinkParamList.add("FLOW_CODE");
       laltLinkParamList.add("file_id=");
       laltLinkParamList.add("FILE_ID");
       lhtColRelationDetail.put(dataView.STR_LINK_PARAM,laltLinkParamList);

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "FILE_DESC");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+ view.getFile_descTitle() +"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"FILE_DESC");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='25%'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "FILE_SIZE");
       lhtColRelationDetail.put(dataView.STR_FIELD_SUB_SQL, "TRUNC(FILE_SIZE/1024)+1");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+ view.getFile_sizeTitle() +"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"FILE_SIZE");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='right'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "UPLOAD_DATE");
       lhtColRelationDetail.put(dataView.STR_FIELD_SUB_SQL, "TO_CHAR(UPLOAD_DATE,'yyyy/mm/dd')");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+ view.getUpload_dateTitle() +"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"UPLOAD_DATE");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='center'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "UPLOAD_EMPNO");
       lhtColRelationDetail.put(dataView.STR_FIELD_SUB_SQL, "(SELECT EMPLOYEE_NAME_C FROM PNL_EMPLOYEE WHERE EMPLOYEE_NO=UPLOAD_EMPNO)");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+view.getUpload_empnoTitle()+"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"UPLOAD_EMPNO");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='center'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "DEL_BTN");
       lhtColRelationDetail.put(dataView.STR_FIELD_SUB_SQL, "''");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'DEL'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "INPUT");
       lhtColRelationDetail.put(dataView.STR_FIELD_HTML,"<INPUT TYPE=\"button\" class=\"button b_normal\" onclick=\"javascript:fc_delfile('%sn%')\" value=");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"'"+view.getBtnDelTitle()+"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='center'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();
     /*
       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "DATA_NO");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+ view.getData_noTitle()+"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"DATA_NO");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='center'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "DATA_STATUS");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+ view.getData_statusTitle()+"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"DATA_STATUS");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='center'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();

       lhtColRelationDetail.put(dataView.STR_FIELD_NAME, "DATA_LINK_NO");
       lhtColRelationDetail.put(dataView.STR_FIELD_TITLE, "'"+view.getData_link_noTitle()+"'");
       lhtColRelationDetail.put(dataView.STR_FIELD_KIND, "0");
       lhtColRelationDetail.put(dataView.STR_FIELD_VALUE,"DATA_LINK_NO");
       lhtColRelationDetail.put(dataView.STR_FIELD_PROPERTY,"width='10%' align='center'");

       laltColRelation.add(new Hashtable<String,Object>(lhtColRelationDetail));
       lhtColRelationDetail.clear();
      */
       return laltColRelation;
   }
   /**
    * getDataViewPageAction
    *
    * @param view View
    * @return String
    */
   private String getDataViewPageAction(View view) {

       StringBuffer stbPageActionURI = new StringBuffer();
       stbPageActionURI.append("sys_upload_file.jsp");
       stbPageActionURI.append("?prgAction=&DB_Alias=");
       stbPageActionURI.append(view.getDB_Alias());
       stbPageActionURI.append("&DBDebug=");
       stbPageActionURI.append(view.getDBDebug());
       stbPageActionURI.append("&SOURCE_CHARSET=");
       stbPageActionURI.append(view.getSourceCharset());
       stbPageActionURI.append("&TARGET_CHARSET=");
       stbPageActionURI.append(view.getTargetCharset());
       stbPageActionURI.append("&flow_code=");
       stbPageActionURI.append(view.getflow_code());
       stbPageActionURI.append("&flow_num=");
       stbPageActionURI.append(view.getflow_num());
       stbPageActionURI.append("&att_table=");
       stbPageActionURI.append(view.getatt_table());
       stbPageActionURI.append("&knd=");
       stbPageActionURI.append(view.getknd());
       stbPageActionURI.append("&att_ifrm_name=");
       stbPageActionURI.append(view.getatt_ifrm_name());
       stbPageActionURI.append("&file_storage=");
       stbPageActionURI.append(view.getfile_storage());
       stbPageActionURI.append("&file_path=");
       stbPageActionURI.append(view.getfile_path());

       return stbPageActionURI.toString();
   }

   /**
    * getDataViewWhere
    *
    * @param view View
    * @return Object
    */
   private String getDataViewWhere(View view) throws Exception{
       StringBuffer stbCondition=new StringBuffer();
       stbCondition.append("att.flow_code=knd.flow_code and att.knd=knd.knd ");
       stbCondition.append("and flow_num='");
       stbCondition.append(view.getflow_num());
       stbCondition.append("'");

       if(view.getknd()!=null && !view.getknd().equals("")){
         stbCondition.append(" and att.knd='");
         stbCondition.append(view.getknd());
         stbCondition.append("'");
       }

       return stbCondition.toString();
   }

  String exeInsSQL(View view) throws Exception {
    StringBuffer stbSQL = new StringBuffer();
    Vector<Object> lvtrParam = new Vector<Object>();
    try {
        //check file_size first
        if(view.getFile_size()==0){
            return view.getMessage("err_msg_file_size_zero");
        }
        if(view.getFile_size() > view.getMax_size()){
                return view.getMessage("err_msg_max_file_size");

        }

      lvtrParam.add(view.getflow_code() == null ? null : view.getflow_code());
      lvtrParam.add(view.getflow_num() == null ? null : view.getflow_num());
      lvtrParam.add(view.getfile_knd() == null ? null :view.getfile_knd());
      lvtrParam.add(view.getflow_num().substring(3) + FormatFunction.addZeros(String.valueOf(PublicFunction.String_to_int(gqbnDAO.readSingleValueData("select nvl(substr(max(file_id),11),0) + 1 from " + view.getatt_table() + " where flow_code='" + view.getflow_code() + "' and flow_num='" + view.getflow_num() + "'"))), 3));
      lvtrParam.add(PublicFunction.getTimestempDate(PublicFunction.getSYSDATE("yyyy/MM/dd HH:mm:ss")));
      lvtrParam.add(view.getMime_type()==null ? null:view.getMime_type());
      lvtrParam.add(view.getFile_size());
      lvtrParam.add(view.getloginUser());
      lvtrParam.add(view.getFile_name());
      lvtrParam.add(view.getfile_desc()==null ? null :view.getfile_desc());
      lvtrParam.add(view.getfile_storage() == null ? null :view.getfile_storage());
      lvtrParam.add(view.getfile_path() == null ? null :view.getfile_path());
      lvtrParam.add("N");//data_status
      lvtrParam.add(gqbnDAO.readSingleValueData("select dqcs_knd from sys_att_kind where flow_code='"+view.getflow_code()+"' and knd='"+view.getknd()+"'"));
      lvtrParam.add(view.getFile_data());

      stbSQL.append("insert into ");
      stbSQL.append(view.getatt_table());
      stbSQL.append(" (flow_code,flow_num,knd,file_id,upload_date,mime_type,file_size,upload_empno,file_name,file_desc,file_storage,file_path,data_status,data_no,file_data) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

      if(gqbnDAO.executeSQL(stbSQL.toString(), lvtrParam)){
          return "";
      }else{return view.getMessage("err_msg_upload_fail");}

    }
    catch (Exception e) {
      e.printStackTrace();
      throw new Exception(e);
    }
    finally {
        if(lvtrParam!=null){
            lvtrParam.clear();
            lvtrParam=null;
        }
    }
  }


  String checkNotNullFld(View view) throws Exception {
    StringBuffer lstbNullFldList = new StringBuffer();
    return (lstbNullFldList.toString().equals("") ? "" :
            lstbNullFldList.
            delete(lstbNullFldList.length() - 1, lstbNullFldList.length()).
            toString());
  }

  void setInitValue(View view) throws Exception {

      view.setFile_kndOptionHTML(WebFunction.transferRSToHtmlOption(gqbnDAO,"select knd,knd_desc from sys_att_kind where flow_code='"+view.getflow_code()+"'"+(view.getknd()!=null && !view.getknd().equals("") ? " and knd='"+view.getknd()+"'":"")+" order by knd",""));
  }
}
